package encaps.utils;

import encaps.entities.Genre;
import encaps.entities.Grant;
import encaps.entities.Role;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class Provider {

    public static boolean isExist(RoleGrantGenreEnum e, String name) throws SQLException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String sq = "SELECT " + e.getPrimColumn() + " FROM " + e.getPrimTable() + " WHERE " + e.getPrimColumn() + "='" + name + "'";
            ps = conn.prepareStatement(sq);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                return false;
            } else {
                return true;
            }
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public static String[] getUnique(RoleGrantGenreEnum e) throws SQLException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        String[] resp;
        StringBuilder sb = new StringBuilder();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String sq = "select distinct " + e.getPrimColumn() + " from " + e.getPrimTable();
            ps = conn.prepareStatement(sq);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                sb.append(rs.getString(e.getPrimColumn()));
                sb.append(" ");
            }
            resp = (sb.toString()).split(" ");
            return resp;
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }

    }

    public static String getUniqueString(RoleGrantGenreEnum e) throws SQLException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String sq = "select distinct " + e.getPrimColumn() + " from " + e.getPrimTable();
            ps = conn.prepareStatement(sq);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                sb.append(rs.getString(e.getPrimColumn()));
                sb.append(";");
            }
            return sb.toString();
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }

    }

    public static String getUniqueString(RoleGrantGenreEnum e, String firstValue) throws SQLException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            String sq = "select distinct " + e.getPrimColumn() + " from " + e.getPrimTable();
            ps = conn.prepareStatement(sq);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String value = rs.getString(e.getPrimColumn());
                if (value.equalsIgnoreCase(firstValue)) {
                    sb.insert(0, ";");
                    sb.insert(0, value);
                } else {
                    sb.append(value);
                    sb.append(";");
                }
            }
            return sb.toString();
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }

    }

    public static void updateName(RoleGrantGenreEnum e, String name, String oldname) throws SQLException, DataAccessException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();

            if (!oldname.equals(name)) {
                //Изменение внешних ключей
                String sq = "UPDATE " + e.getForTable() + " SET " + e.getForColumn() + "=" + name + " WHERE " + e.getForColumn() + "='" + oldname + "'";
                ps = conn.prepareStatement(sq);
                if (ps.executeUpdate() != 1) {
                    // throw new SQLException("Update error");
                }
                //Изменение основной таблицы
                sq = "UPDATE " + e.getPrimColumn() + " SET " + e.getPrimColumn() + "=" + name + " WHERE " + e.getPrimColumn() + "='" + oldname + "'";
                ps = conn.prepareStatement("UPDATE ? SET ?=? WHERE ?=?");
                ps.setString(1, e.getPrimTable());
                ps.setString(2, e.getPrimColumn());
                ps.setString(3, name);
                ps.setString(4, e.getPrimColumn());
                ps.setString(5, oldname);
                if (ps.executeUpdate() != 1) {
                    throw new SQLException("Update error");
                }
            }
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public static void insertValue(RoleGrantGenreEnum e, String name) throws SQLException, DataAccessException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            if (isExist(e, name)) {
                throw new DataAccessException("Already exist");
            }
            conn = dataSource.getConnection();
            String sq = "INSERT INTO " + e.getPrimTable() + " ('" + e.getPrimColumn() + "') " + "VALUES('" + name + "')";
            ps = conn.prepareStatement(sq);
            if (ps.executeUpdate() != 1) {
                throw new SQLException("Insert error");
            }
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public static void deleteValues(RoleGrantGenreEnum e, String name) throws SQLException, DataAccessException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();

            //Удаление внешних ключей
            String sq = "DELETE FROM " + e.getForTable() + " WHERE " + e.getForColumn() + "='" + name + "'";
            ps = conn.prepareStatement(sq);
            ps.executeUpdate();
            //Удаление из основной таблицы
            sq = "DELETE FROM " + e.getPrimTable() + " WHERE " + e.getPrimColumn() + "='" + name + "'";
            ps = conn.prepareStatement(sq);
            if (ps.executeUpdate() != 1) {
                throw new SQLException("Delete error");
            }
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public static void updateRole(String oldname, Role r) throws SQLException, DataAccessException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            if (isExist(RoleGrantGenreEnum.ROLE, oldname)) {
                //throw new DataAccessException("Result not found");
                ps = conn.prepareStatement("DELETE FROM role_grant WHERE role_name=?");
                ps.setString(1, oldname);
                if (ps.executeUpdate() != 1) {
                    //throw new SQLException("Delete error");
                }
            }
            if (!oldname.equals(r.getRoleName())) {
                ps = conn.prepareStatement("UPDATE role SET name=? WHERE name=?");
                ps.setString(1, oldname);
                ps.setString(2, r.getRoleName());
                if (ps.executeUpdate() != 1) {
                    //throw new SQLException("Update error");
                }
            }
            ps = conn.prepareStatement("INSERT INTO role_grant(role_name,grant_name) VALUES(?,?) ");
            String[] grants = r.getGrantsAsString().split(";");
            for (int i = 0; i < grants.length; i++) {
                ps.setString(1, r.getRoleName());
                ps.setString(2, grants[i]);
                if (ps.executeUpdate() != 1) {
                    // throw new SQLException("Insert error");
                }
            }
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public static void createRole(Role r) throws SQLException, DataAccessException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            if (isExist(RoleGrantGenreEnum.ROLE, r.getRoleName())) {
                throw new DataAccessException("Role already exist");
            }
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("INSERT INTO role(name) VALUES(?)");
            ps.setString(1, r.getRoleName());
            if (ps.executeUpdate() != 1) {
                throw new SQLException("Insert error");
            }

            ps = conn.prepareStatement("INSERT INTO role_grant(role_name,grant_name) VALUES(?,?) ");
            String[] grants = r.getGrantsAsString().split(";");
            for (int i = 0; i < grants.length; i++) {
                ps.setString(1, r.getRoleName());
                ps.setString(2, grants[i]);
                if (ps.executeUpdate() != 1) {
                    throw new SQLException("Insert error");
                }
            }
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public static void createGrant(Grant g) throws SQLException, DataAccessException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            if (isExist(RoleGrantGenreEnum.GRANT, g.getGrantName())) {
                throw new DataAccessException("grant already exist");
            }
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("INSERT INTO grant(name) VALUES(?)");
            ps.setString(1, g.getGrantName());
            if (ps.executeUpdate() != 1) {
                throw new SQLException("Insert error");
            }
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }

    public static void createGenre(Genre g) throws SQLException, DataAccessException, NamingException {
        DataSource dataSource = DatabaseUtil.getDataSource();
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            if (isExist(RoleGrantGenreEnum.GENRE, g.getName())) {
                throw new DataAccessException("Genre already exist");
            }
            conn = dataSource.getConnection();
            ps = conn.prepareStatement("INSERT INTO genre(name) VALUES(?)");
            ps.setString(1, g.getName());
            if (ps.executeUpdate() != 1) {
                throw new SQLException("Insert error");
            }
        } finally {
            DatabaseUtil.closeConnection(conn, ps);
        }
    }
}
